create or replace view VIEW_YBXX_JI_ZHONG_ZI_JIN as
select t1.*,t2.name,t2.pk_financeorg,t2.pk_fatherorg,t2.tree_level,t2.tree_path from (select nvl(t1.pk_org, t2.pk_org) as pk_org,
               nvl(t1.pk_org_r, t2.pk_org_p) as pk_org_lower,
               t1.successtotal as XIA_BO_JIN_E,
               t2.successtotal as SHANG_SHOU_JIN_E
          FROM (select sf_allocate_h.pk_org,
                       T1.pk_org_r,
                       sum(sf_allocate_h.successtotal) successtotal
                  from sf_allocate_h sf_allocate_h
                 inner join sf_allocate_b T1
                    ON T1.pk_allocate_h = sf_allocate_h.pk_allocate_h
                 where (sf_allocate_h.billstatus = 5)
                   and sf_allocate_h.dr = 0
                   and T1.dr = 0
                   and nvl(sf_allocate_h.dr, 0) = 0
                 group by sf_allocate_h.pk_org, T1.pk_org_r) t1
        
          full join
        
         (select sf_delivery_h.pk_org,
                sum(sf_delivery_h.successtotal) successtotal,
                T1.pk_org_p
           from sf_delivery_h sf_delivery_h
          inner join sf_delivery_b T1
             ON T1.pk_delivery_h = sf_delivery_h.pk_delivery_h
          where (sf_delivery_h.billstatus = 4)
            and sf_delivery_h.dr = 0
            and T1.dr = 0
            and nvl(sf_delivery_h.dr, 0) = 0
          group by sf_delivery_h.pk_org, T1.pk_org_p) t2
            on t1.pk_org = t2.pk_org
           and t1.pk_org_r = t2.pk_org_p) t1
           left join
           (
           select t.name,t.pk_financeorg,t.pk_fatherorg, level as tree_level,SYS_CONNECT_BY_PATH(t.PK_FINANCEORG,'\') as tree_path
                      from (

                            select code,
                                    name,
                                    pk_financeorg,
                                    pk_fatherorg,
                                    pk_corp,
                                    pk_group,
                                    enablestate
                              from (SELECT org_corp.code,
                                            org_corp.name,
                                            org_corp.pk_corp pk_financeorg,
                                            org_corp.pk_fatherorg,
                                            org_corp.pk_corp,
                                            org_corp.pk_group,
                                            org_financeorg.enablestate
                                       FROM org_corp
                                       LEFT outer JOIN org_financeorg
                                         ON org_corp.pk_corp =
                                            org_financeorg.pk_financeorg
                                      WHERE org_corp.pk_corp IN
                                            (SELECT pk_org
                                               FROM org_orgs
                                              WHERE orgtype5 = 'Y')
                                     UNION
                                     SELECT org_financeorg.code,
                                            org_financeorg.name,
                                            org_financeorg.pk_financeorg,
                                            org_orgs.pk_corp pk_fatherorg,
                                            org_orgs.pk_corp,
                                            org_financeorg.pk_group,
                                            org_financeorg.enablestate
                                       FROM org_financeorg
                                       LEFT outer JOIN org_orgs
                                         ON org_financeorg.pk_financeorg =
                                            org_orgs.pk_org
                                      WHERE orgtype5 = 'Y'
                                        AND org_financeorg.pk_financeorg <>
                                            org_orgs.pk_corp) org_financeorg_temp
                             where 11 = 11
                               and (enablestate = 2)
                               and ((pk_group =
                                   (select pk_group
                                        from org_corp
                                       where pk_fatherorg = '~')))
                             order by code) t
                     start with pk_financeorg =
                                (select pk_corp
                                   from org_corp
                                  where pk_fatherorg = '~')
                    connect by prior pk_financeorg = pk_fatherorg
                     order by level
                     ) t2
                     on t1.pk_org_lower = t2.pk_financeorg;
